package com.sxhnpg.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.log4j.Logger;

/**
 * 自动创建月表类
 * @author ChenHW
 *
 */

public class CreateMonthTable {
	private static Logger logger = Logger.getLogger(CreateMonthTable.class);  

	// 创建月表
	public static void createMonthTable(){
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			con.setAutoCommit(false);
		
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM");
			Calendar cl = Calendar.getInstance();
			String currentMonthTableName = "sxhnpg_agency_" + sdf.format(cl.getTime());
			
			createTableByName(con,currentMonthTableName);	// 创建本月月表
			// 将本月表名插入数据库
			if(!findTable(con,currentMonthTableName)){
				insertTable(con,currentMonthTableName);
			}
			
			cl.add(Calendar.MONTH, 1);
			String nextMonthTableName = "sxhnpg_agency_" + sdf.format(cl.getTime());
			
			createTableByName(con,nextMonthTableName);	// 创建下一个月月表
			// 将下个月表名插入数据库
			if(!findTable(con,nextMonthTableName)){
				insertTable(con,nextMonthTableName);
			}
			
			con.commit();	// 提交事务
			con.close();	// 关闭连接
			
			logger.info("月表创建成功");
			
		}catch(Exception e){
			try {
				con.rollback();
			}catch (SQLException sqlError) {
				logger.error("回滚失败：" + sqlError);
			}
			logger.error("创建月表失败：" + e);
		}
	}
	
	// 创建表sql
	public static void createTableByName(Connection con , String tableName) throws SQLException{
		String sql = "CREATE TABLE IF NOT EXISTS `" + tableName + "` ( " +
		  " `AGENCY_ID` int(11) NOT NULL AUTO_INCREMENT, " +
		  " `TRAVEL_AGENCY_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `CAR_NUMBER` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `CAR_TYPE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `COUNT_PEOPLE` int(11) DEFAULT NULL, " +
		  " `CITY` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `VIEWPOINT` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `IS_DRIVER` int(11) DEFAULT NULL, " +
		  " `DRIVER` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `GUIDE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `IS_CONTRACT` int(11) DEFAULT NULL, " +
		  " `REGISTER_DATE` datetime NOT NULL, " +
		  " `REMAIN_TIME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `SUBSIDY_PRICE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `TRAVELER_PRICE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `OPERATE_PEOPLE` varchar(100) COLLATE utf8_unicode_ci NOT NULL, " +
		  " `LAST_PLACE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `MOBILE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `PAY_TYPE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `TOTAL_PRICE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " `HANDLE` int(11) NOT NULL DEFAULT '0', " +
		  " `IS_DEDICATED` varchar(50)  COLLATE utf8_unicode_ci DEFAULT NULL, " +
		  " KEY `AGENCY_ID` (`AGENCY_ID`) " +
		  " ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='month table' AUTO_INCREMENT=1; ";
		PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
		pstmt.executeUpdate(sql);
		pstmt.close();
		
	}
	
	// 根据月表名查询是否存在
	public static boolean findTable(Connection con , String tableName) throws SQLException{
		boolean isExit = false;
		String sql = "SELECT * FROM snhnpg_month_table_name " +
			" WHERE MONTH_TABLE_NAME = '"+ tableName +"'";
		PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
		ResultSet rs = pstmt.executeQuery(sql);
		if(rs.next()){
			isExit = true;
		}
		rs.close();
		pstmt.close();
		return isExit;
	}
	
	// 插入月表名
	public static void insertTable(Connection con , String tableName) throws SQLException{
		String sql = "INSERT INTO snhnpg_month_table_name (MONTH_TABLE_NAME) VALUES('"+ tableName +"')";
		PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
//		pstmt.setTimestamp(1, new Timestamp(new Date().getTime()));
		pstmt.execute(sql);
		pstmt.close();
	}
	
	// 单元测试
	public static void main(String[] args){
		try{
			createMonthTable();
		}catch(Exception e){
			System.out.println(e);
		}
	}
}
